import pymysql

#获取数据库的版本号
def OperMysql():
    print("进入打开数据库函数")
    #打开数据库
    db = pymysql.connect(host="localhost",user="root",password="root",database="jssc1")
    #声明操作数据库的游标
    curses = db.cursor()
    #通过游标执行SQL语句
    curses.execute("select version()")
    #获取执行后的数据
    data = curses.fetchone()
    #打印输出
    print(data)
    #关闭数据库
    db.close()

#创建数据库中的表
def createTable():
    db = pymysql.connect(host="localhost",user="root",password="root",database="jssc1")

    curses = db.cursor()
    curses.execute("drop table if exists employee")
    sql = """create table employee(
        id int primary key,
        firstname char(20) not null,
        age int)
        """
    curses.execute(sql)
    db.close()

#插入数据
def insertEmp():
    db = pymysql.connect(host="localhost", user="root", password="root", database="jssc1")
    curses = db.cursor()
    sql = "insert into employee(id,firstname,age) value(8,'aa',20)"
    try:
        # 执行操作语句
        curses.execute(sql)
        # 操作语句必须进行提交
        db.commit()
    except:
        #执行操作语句失败时的异常处理
        db.rollback()
    db.close()

#查询表
def selectEmp():
    db = pymysql.connect(host="localhost", user="root", password="root", database="jssc1")
    curses = db.cursor()
    sql = "select * from employee"
    try:
        curses.execute(sql)
        #接受查询后的数据
        res = curses.fetchall()
        for row in res:
            id = row[0]
            name = row[1]
            age = row[2]
            print("id=%s,name=%s,age=%s"%(id,name,age))

    except:
        print("读取数据失败")
    db.close()

#更新数据
def updateEmp(myId):
    print(myId)
    db = pymysql.connect(host="localhost", user="root", password="root", database="jssc1")
    curses = db.cursor()
    #对不同类型拼接字符串时要转换为相同类型的数据
    sql = "update employee set age=age+1 where id = "+ str(myId)
    try:
        curses.execute(sql)
        db.commit()
    except:
        db.rollback()
        print("更新数据失败")
    db.close()

#删除数据
def delEmp():
    #打开数据库连接
    db = pymysql.connect(host="localhost", user="root", password="root", database="jssc1")
    cursor = db.cursor()

    #SQL 删除语句
    sql = "DELETE FROM EMPLOYEE WHERE AGE > %s" %(20)
    try:
        cursor.execute(sql)
        db.commit()
    except:
        db.rollback()

    db.close()